package com.bsh.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.RequestParam;

import com.bsh.api.ApiUtils;
import com.bsh.api.params.DtoMerchantInfo;
import com.bsh.api.params.DtoShop;
import com.bsh.mybatis.IRepository.MerchantMapper;
import com.bsh.mybatis.dataView.MerchantImages;
import com.bsh.mybatis.model.Merchant;
import com.bsh.utils.Pager;
import com.mysql.jdbc.StringUtils;

@Repository
public class DaoMerchant {
	@Resource
	SqlSessionTemplate session;
	MerchantMapper getMerchantMapper(){
		return session.getMapper(MerchantMapper.class);
	}
	@Resource
	JdbcTemplate jdbcTemplate;
	@Resource
	NamedParameterJdbcTemplate namedJdbcTemplate;

	MerchantMapper getMapper() {
		return session.getMapper(MerchantMapper.class);
	}

	public int deleteByPrimaryKey(Integer id) {
		int res = getMapper().deleteByPrimaryKey(id);
		return res;
	}

	public int insert(Merchant record) {
		int res = getMapper().insert(record);
		return record.getId();
	}

	public int insertSelective(Merchant record) {
		int res = getMapper().insertSelective(record);
		return record.getId();
	}

	public Merchant selectByPrimaryKey(Integer id) {
		Merchant row = getMapper().selectByPrimaryKey(id);
		return row;
	}

	public int updateByPrimaryKeySelective(Merchant record) {
		int res = getMapper().updateByPrimaryKeySelective(record);
		return res;
	}

	public int updateByPrimaryKey(Merchant record) {
		int res = getMapper().updateByPrimaryKey(record);
		return res;
	}

	/**
	 * 分页查询
	 */
	public List<Map<String, Object>> getPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select m.*,cat.name category_name,u.name user_name,rp.name province_name,rc.name city_name,ra.name area_name ");
		sb.append("from merchant m ");
		sb.append("join user u on u.id=m.userid ");
		sb.append("left join merchant_category cat on cat.id=m.category ");
		sb.append("left join region rp on rp.id=m.province ");
		sb.append("left join region rc on rc.id=m.city ");
		sb.append("left join region ra on ra.id=m.area ");
		sb.append("where status=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by m.recmd_sort desc,m.audit_time desc,m.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * 分页查询导出
	 */
	public List<Map<String, Object>> getPageExport(String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select m.*,cat.name category_name,u.name user_name,rp.name province_name,rc.name city_name,ra.name area_name,");
		sb.append("DATE_FORMAT(m.create_time,'%Y-%m-%d %H:%i') create_time_ch,");
		sb.append("case"); 
		sb.append("  WHEN m.status='0' then '待审核'");
		sb.append("  WHEN m.status='1' then '已通过审核'");
		sb.append("  WHEN m.status='2' then '未通过审核'");
		sb.append("end status_ch");		
		sb.append(" from merchant m ");
		sb.append("join user u on u.id=m.userid ");
		sb.append("left join merchant_category cat on cat.id=m.category ");
		sb.append("left join region rp on rp.id=m.province ");
		sb.append("left join region rc on rc.id=m.city ");
		sb.append("left join region ra on ra.id=m.area ");
		sb.append("where status=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}

		sb.append(" order by m.audit_time desc,m.id desc");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		return list;
	}

	/**
	 * 商家申请分页查询
	 */
	public List<Map<String, Object>> getAskPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select m.*,cat.name category_name,u.name user_name,rp.name province_name,rc.name city_name,ra.name area_name from merchant m ");
		sb.append("join user u on u.id=m.userid ");
		sb.append("left join merchant_category cat on cat.id=m.category ");
		sb.append("left join region rp on rp.id=m.province ");
		sb.append("left join region rc on rc.id=m.city ");
		sb.append("left join region ra on ra.id=m.area ");
		sb.append("where (status=0 or status=2) ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by m.id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}

	/**
	 * 添加商户
	 * @param userid
	 * @return
	 */
	public Integer addMerchant(Merchant merchant){
		MerchantMapper map = this.getMapper();
		return map.addMerchant(merchant);
	}
	
	/**
	 * 审核
	 * @param userid
	 * @return
	 */
	public Integer audit(int merchantId, boolean isPass, String auditMsg) {
		MerchantMapper map = this.getMapper();
		if (isPass)
			return map.auditPass(merchantId, new Date());
		else
			return map.auditRefuse(merchantId, auditMsg, new Date());
	}
	
	/**
	 * 修改商家,不更新照片
	 * @param merchant
	 * @return
	 */
	public Integer editMerchantExludeImages(Merchant merchant) {
		MerchantMapper map = this.getMapper();
		int mt = map.editMerchantExludeImages(merchant);
		return mt;
	}
	
	/**
	 * 修改商家证照
	 * @param merchant
	 * @return
	 */
	public Integer editMerchantImages(MerchantImages images) {
		MerchantMapper map = this.getMapper();
		int mt = map.editMerchantImages(images);
		return mt;
	}
	
	/**
	 * 取得商家证照
	 * @param merchant
	 * @return
	 */
	public MerchantImages getMerchantImages(int id) {	
		MerchantMapper map = this.getMapper();
		return map.getMerchantImages(id);
	}

	/**
	 * api 商铺列表
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> apiGetShopList(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
//		sb.append("select m.*,cat.name category_name,u.name user_name,rp.name province_name,rc.name city_name,ra.name area_name from merchant m ");
		sb.append("select m.id,m.linkman,m.merchant_name as merchantName,m.address,m.mobile,m.img_cover as imgCover,m.img_cover_small as imgCoverSmall,m.likes,m.trader_desc as traderDesc,"
				+ "ifnull(m1.url,'') as showImg1,ifnull(m2.url,'') as showImg2 from merchant m ");
		sb.append("join user u on u.id=m.userid ");
		sb.append("LEFT JOIN merchant_category cat on cat.id=m.category ");
		sb.append("LEFT JOIN region rp on rp.id=m.province ");
		sb.append("LEFT JOIN region rc on rc.id=m.city ");
		sb.append("LEFT JOIN region ra on ra.id=m.area ");
		sb.append("LEFT JOIN (SELECT merchant_id,url from  merchant_img where sort=1) m1 on m.id=m1.merchant_id ");
		sb.append("LEFT JOIN (SELECT merchant_id,url from  merchant_img where sort=2) m2 on m.id=m2.merchant_id ");
		
		
		sb.append("where status=1 ");
		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by m.create_time desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * apiH5 商铺列表
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> apiH5GetShopList(Pager pager, DtoShop qryParams) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
        sb.append("select m.id,m.merchant_name as merchantName,m.trader_desc as traderDesc"
        		+ ",m.img_cover as imgCover,m.img_cover_small as imgCoverSmall  from merchant m ");
		sb.append("where  m.status=1 ");
		if (ApiUtils.objIsNotEmpty(qryParams.getKeyword()))
			sb.append(String.format(" and m.merchant_name like '%%%s%%' ", qryParams.getKeyword().trim()));
		if (qryParams.getCategory() != null && qryParams.getCategory() >0 )
			sb.append(String.format(" and m.category = %d ", qryParams.getCategory()));
		if (qryParams.getCity() != null && qryParams.getCity() >0 )
			sb.append(String.format(" and m.city = %d ", qryParams.getCity()));
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		sb.append(" order by m.create_time desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * apiH5 热门商铺列表
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> apiH5GetHotShopList() {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
        sb.append("select m.id,m.merchant_name as merchantName,m.trader_desc as traderDesc"
        		+ ",m.img_cover as imgCover,m.img_cover_small as imgCoverSmall  from merchant m ");
		sb.append("where m.status=1 and m.recmd_sort>0");
		sb.append(" order by m.create_time desc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	
	
	/**
	 * h5商铺详情
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> apiH5GetShopDetail(String merchantId) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select m.id,m.linkman,m.merchant_name as merchantName,m.address,m.mobile,m.img_cover as imgCover,m.img_cover_small as imgCoverSmall,m.likes,m.trader_desc as traderDesc,"
				+ "ifnull(m1.url,'') as showImg1,ifnull(m2.url,'') as showImg2 from merchant m ");
		sb.append("join user u on u.id=m.userid ");
		sb.append("LEFT JOIN merchant_category cat on cat.id=m.category ");
		sb.append("LEFT JOIN region rp on rp.id=m.province ");
		sb.append("LEFT JOIN region rc on rc.id=m.city ");
		sb.append("LEFT JOIN region ra on ra.id=m.area ");
		sb.append("LEFT JOIN (SELECT merchant_id,url from  merchant_img where sort=1) m1 on m.id=m1.merchant_id ");
		sb.append("LEFT JOIN (SELECT merchant_id,url from  merchant_img where sort=2) m2 on m.id=m2.merchant_id ");
		sb.append(" where m.status=1 ");
		if (!StringUtils.isNullOrEmpty(merchantId)) {
			sb.append(" and m.id="+merchantId);
		}
		sb.append(" order by m.create_time desc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	
	/**
	 * api 热门商铺列表
	 * @param pager
	 * @param where
	 * @return
	 */
	public List<Map<String, Object>> apiGetHotShopList() {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select m.id,m.linkman,m.merchant_name as merchantName,m.address,m.mobile,m.img_cover as imgCover,m.img_cover_small as imgCoverSmall,m.likes,m.trader_desc as traderDesc,"
				+ "ifnull(m1.url,'') as showImg1,ifnull(m2.url,'') as showImg2 from merchant m ");
		sb.append("join user u on u.id=m.userid ");
		sb.append("LEFT JOIN merchant_category cat on cat.id=m.category ");
		sb.append("LEFT JOIN region rp on rp.id=m.province ");
		sb.append("LEFT JOIN region rc on rc.id=m.city ");
		sb.append("LEFT JOIN region ra on ra.id=m.area ");
		sb.append("LEFT JOIN (SELECT * from  merchant_img where sort=1) m1 on m.id=m1.merchant_id ");
		sb.append("LEFT JOIN (SELECT * from  merchant_img where sort=2) m2 on m.id=m2.merchant_id ");
		sb.append("where status=1 and m.recmd_sort>0");
		sb.append(" order by m.recmd_sort desc ");
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * api 增加商户
	 * @param merchant
	 * @return
	 */
	public Integer apiAddMerchant(Merchant merchant){
		MerchantMapper map = this.getMapper();
		return map.apiAddMerchant(merchant);
	}
	
	/**
	 * 按用户名查询商户
	 * @param userUame
	 * @return
	 */
	public Merchant getByName(String userUame){
		return this.getMapper().getByName(userUame);
	}
	
	/**
	 * 根据名称查询商家
	 * @param name
	 * @return
	 */
	public Merchant getMerchantByName(String name){
		return this.getMapper().getMerchantByName(name);
	}
	
	/**
	 * 根据商家全称查询商家
	 * @param MerchantName
	 * @return
	 */
	public Merchant getMerchantByMerchantName(String MerchantName){
		return this.getMapper().getMerchantByMerchantName(MerchantName);
	}
	
	/**
	 * 根据userId 查属于某用户的商家list
	 * @param userId
	 * @return
	 */
	public List<Map<String, Object>> getMerchantByUserId(Integer userId) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT m.*,m1.id as imgId1,m2.id as imgId2,cat.name AS categoryName,ifnull(m1.url,'') as showImg1 ,ifnull(m2.url,'') as showImg2 ,");
		sb.append(" rp.name AS provinceName,rc.name as cityName, ra.name AS areaName FROM merchant m ");
 		sb.append(" JOIN user u on u.id=m.userid ");
		sb.append(" LEFT JOIN merchant_category cat on cat.id=m.category ");
		sb.append(" LEFT JOIN region rp on rp.id=m.province ");
		sb.append(" LEFT JOIN region rc on rc.id=m.city ");
		sb.append(" LEFT JOIN region ra on ra.id=m.area ");
		sb.append(" LEFT JOIN (select * from  merchant_img where sort=1)  m1 on m.id=m1.merchant_id ");
		sb.append(" LEFT JOIN (select * from  merchant_img where sort=2)  m2 on m.id=m2.merchant_id ");
		sb.append(String.format(" WHERE m.userid = %d ", userId ));
		
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 根据id查询商户详情
	 * @param id
	 * @return
	 */
	public List<DtoMerchantInfo> getMerchantDetailById (Integer id) {
		return this.getMapper().getMerchantDetailById(id);
	}
	
	/**
	 * 根据userId 查属于某用户状态为通过的商家list
	 * 商家为激活的，正常的状态status=1 
	 * !!status=1  , 使用于推荐奖励Recommend
	 * @param userId
	 * @return
	 */
	public List<Map<String, Object>> getValidMerchantByUserId(Integer userId) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append(" SELECT m.*,m1.id as imgId1,m2.id as imgId2,cat.name AS categoryName,ifnull(m1.url,'') as showImg1 ,ifnull(m2.url,'') as showImg2 ,");
		sb.append(" rp.name AS provinceName,rc.name as cityName, ra.name AS areaName FROM merchant m ");
 		sb.append(" JOIN user u on u.id=m.userid ");
		sb.append(" LEFT JOIN merchant_category cat on cat.id=m.category ");
		sb.append(" LEFT JOIN region rp on rp.id=m.province ");
		sb.append(" LEFT JOIN region rc on rc.id=m.city ");
		sb.append(" LEFT JOIN region ra on ra.id=m.area ");
		sb.append(" LEFT JOIN (select * from  merchant_img where sort=1)  m1 on m.id=m1.merchant_id ");
		sb.append(" LEFT JOIN (select * from  merchant_img where sort=2)  m2 on m.id=m2.merchant_id ");
		sb.append(String.format(" WHERE m.userid = %d and m.status=1 ", userId ));
		
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);
		return list;
	}
	
	/**
	 * 设置推荐排序 
	 * @param id
	 * @param sort
	 * @return
	 */
	public Integer setRecomendSort(@RequestParam(value = "id") Integer id,@RequestParam(value = "sort") Integer sort){
		return getMapper().setRecomendSort(id, sort);
	}
}
